<!DOCTYPE html>
<html lang="zh-CN">





<head>
  <meta charset="UTF-8">
  <link rel="apple-touch-icon" sizes="76x76" href="/michuanblog/img/favicon.png">
  <link rel="icon" type="image/png" href="/michuanblog/img/favicon.png">
  <meta name="viewport"
        content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no, shrink-to-fit=no">
  <meta http-equiv="x-ua-compatible" content="ie=edge">
  
  <meta name="theme-color" content="#2f4154">
  <meta name="description" content="Coding">
  <meta name="author" content="胡晗">
  <meta name="keywords" content="">
  <title>50道SQL查询语句练习 - 胡晗&#39;s Blog</title>

  <link  rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/4.4.1/css/bootstrap.min.css" />


  <link  rel="stylesheet" href="https://cdn.staticfile.org/github-markdown-css/4.0.0/github-markdown.min.css" />
  <link  rel="stylesheet" href="/michuanblog/lib/hint/hint.min.css" />

  
    <link  rel="stylesheet" href="https://cdn.staticfile.org/highlight.js/10.0.0/styles/github-gist.min.css" />
  

  


<!-- 主题依赖的图标库，不要自行修改 -->

<link rel="stylesheet" href="//at.alicdn.com/t/font_1749284_yg9cfy8wd6.css">



<link rel="stylesheet" href="//at.alicdn.com/t/font_1736178_pjno9b9zyxs.css">


<link  rel="stylesheet" href="/michuanblog/css/main.css" />

<!-- 自定义样式保持在最底部 -->


  <script  src="/michuanblog/js/utils.js" ></script>
<meta name="generator" content="Hexo 4.2.1"></head>


<body>
  <header style="height: 70vh;">
    <nav id="navbar" class="navbar fixed-top  navbar-expand-lg navbar-dark scrolling-navbar">
  <div class="container">
    <a class="navbar-brand"
       href="/michuanblog/">&nbsp;<strong>MiChuan</strong>&nbsp;</a>

    <button id="navbar-toggler-btn" class="navbar-toggler" type="button" data-toggle="collapse"
            data-target="#navbarSupportedContent"
            aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
      <div class="animated-icon"><span></span><span></span><span></span></div>
    </button>

    <!-- Collapsible content -->
    <div class="collapse navbar-collapse" id="navbarSupportedContent">
      <ul class="navbar-nav ml-auto text-center">
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/michuanblog/">
                <i class="iconfont icon-home-fill"></i>
                首页
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/michuanblog/archives/">
                <i class="iconfont icon-archive-fill"></i>
                归档
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/michuanblog/categories/">
                <i class="iconfont icon-category-fill"></i>
                分类
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/michuanblog/tags/">
                <i class="iconfont icon-tags-fill"></i>
                标签
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/michuanblog/about/">
                <i class="iconfont icon-user-fill"></i>
                关于
              </a>
            </li>
          
        
        
          <li class="nav-item" id="search-btn">
            <a class="nav-link" data-toggle="modal" data-target="#modalSearch">&nbsp;&nbsp;<i
                class="iconfont icon-search"></i>&nbsp;&nbsp;</a>
          </li>
        
      </ul>
    </div>
  </div>
</nav>

    <div class="view intro-2" id="background" parallax=true
         style="background: url('/michuanblog/img/default.png') no-repeat center center;
           background-size: cover;">
      <div class="full-bg-img">
        <div class="mask flex-center" style="background-color: rgba(0, 0, 0, 0.3)">
          <div class="container text-center white-text fadeInUp">
            <span class="h2" id="subtitle">
              
            </span>

            
              
  <div class="mt-3 post-meta">
    <i class="iconfont icon-date-fill" aria-hidden="true"></i>
    <time datetime="2020-08-14 12:13">
      2020年8月14日 中午
    </time>
  </div>


<div class="mt-1">
  
    
    <span class="post-meta mr-2">
      <i class="iconfont icon-chart"></i>
      4.4k 字
    </span>
  

  
    
    <span class="post-meta mr-2">
      <i class="iconfont icon-clock-fill"></i>
      
      
      64
       分钟
    </span>
  

  
  
</div>

            
          </div>

          
        </div>
      </div>
    </div>
  </header>

  <main>
    
      

<div class="container-fluid">
  <div class="row">
    <div class="d-none d-lg-block col-lg-2"></div>
    <div class="col-lg-8 nopadding-md">
      <div class="container nopadding-md" id="board-ctn">
        <div class="py-5" id="board">
          <div class="post-content mx-auto" id="post">
            
            <article class="markdown-body">
              <h2 id="数据表介绍"><a href="#数据表介绍" class="headerlink" title="数据表介绍"></a>数据表介绍</h2><ol>
<li><p>学生表<br>Student(SId,Sname,Sage,Ssex)<br>SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别</p>
</li>
<li><p>课程表<br>Course(CId,Cname,TId)<br>CId 课程编号,Cname 课程名称,TId 教师编号</p>
</li>
<li><p>教师表<br>Teacher(TId,Tname)<br>TId 教师编号,Tname 教师姓名</p>
</li>
<li><p>成绩表<br>SC(SId,CId,score)<br>SId 学生编号,CId 课程编号,score 分数</p>
</li>
</ol>
<h2 id="插入数据"><a href="#插入数据" class="headerlink" title="插入数据"></a>插入数据</h2><h3 id="学生表-Student"><a href="#学生表-Student" class="headerlink" title="学生表 Student"></a>学生表 Student</h3><pre><code class="hljs sql"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> Student(<span class="hljs-keyword">SId</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">10</span>),Sname <span class="hljs-built_in">varchar</span>(<span class="hljs-number">10</span>),Sage datetime,Ssex <span class="hljs-built_in">varchar</span>(<span class="hljs-number">10</span>));
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> Student <span class="hljs-keyword">values</span>(<span class="hljs-string">'01'</span> , <span class="hljs-string">'赵雷'</span> , <span class="hljs-string">'1990-01-01'</span> , <span class="hljs-string">'男'</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> Student <span class="hljs-keyword">values</span>(<span class="hljs-string">'02'</span> , <span class="hljs-string">'钱电'</span> , <span class="hljs-string">'1990-12-21'</span> , <span class="hljs-string">'男'</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> Student <span class="hljs-keyword">values</span>(<span class="hljs-string">'03'</span> , <span class="hljs-string">'孙风'</span> , <span class="hljs-string">'1990-12-20'</span> , <span class="hljs-string">'男'</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> Student <span class="hljs-keyword">values</span>(<span class="hljs-string">'04'</span> , <span class="hljs-string">'李云'</span> , <span class="hljs-string">'1990-12-06'</span> , <span class="hljs-string">'男'</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> Student <span class="hljs-keyword">values</span>(<span class="hljs-string">'05'</span> , <span class="hljs-string">'周梅'</span> , <span class="hljs-string">'1991-12-01'</span> , <span class="hljs-string">'女'</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> Student <span class="hljs-keyword">values</span>(<span class="hljs-string">'06'</span> , <span class="hljs-string">'吴兰'</span> , <span class="hljs-string">'1992-01-01'</span> , <span class="hljs-string">'女'</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> Student <span class="hljs-keyword">values</span>(<span class="hljs-string">'07'</span> , <span class="hljs-string">'郑竹'</span> , <span class="hljs-string">'1989-01-01'</span> , <span class="hljs-string">'女'</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> Student <span class="hljs-keyword">values</span>(<span class="hljs-string">'09'</span> , <span class="hljs-string">'张三'</span> , <span class="hljs-string">'2017-12-20'</span> , <span class="hljs-string">'女'</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> Student <span class="hljs-keyword">values</span>(<span class="hljs-string">'10'</span> , <span class="hljs-string">'李四'</span> , <span class="hljs-string">'2017-12-25'</span> , <span class="hljs-string">'女'</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> Student <span class="hljs-keyword">values</span>(<span class="hljs-string">'11'</span> , <span class="hljs-string">'李四'</span> , <span class="hljs-string">'2012-06-06'</span> , <span class="hljs-string">'女'</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> Student <span class="hljs-keyword">values</span>(<span class="hljs-string">'12'</span> , <span class="hljs-string">'赵六'</span> , <span class="hljs-string">'2013-06-13'</span> , <span class="hljs-string">'女'</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> Student <span class="hljs-keyword">values</span>(<span class="hljs-string">'13'</span> , <span class="hljs-string">'孙七'</span> , <span class="hljs-string">'2014-06-01'</span> , <span class="hljs-string">'女'</span>);</code></pre>
<h3 id="科目表-Course"><a href="#科目表-Course" class="headerlink" title="科目表 Course"></a>科目表 Course</h3><pre><code class="hljs sql"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> Course(CId <span class="hljs-built_in">varchar</span>(<span class="hljs-number">10</span>),Cname <span class="hljs-keyword">nvarchar</span>(<span class="hljs-number">10</span>),TId <span class="hljs-built_in">varchar</span>(<span class="hljs-number">10</span>));
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> Course <span class="hljs-keyword">values</span>(<span class="hljs-string">'01'</span> , <span class="hljs-string">'语文'</span> , <span class="hljs-string">'02'</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> Course <span class="hljs-keyword">values</span>(<span class="hljs-string">'02'</span> , <span class="hljs-string">'数学'</span> , <span class="hljs-string">'01'</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> Course <span class="hljs-keyword">values</span>(<span class="hljs-string">'03'</span> , <span class="hljs-string">'英语'</span> , <span class="hljs-string">'03'</span>);</code></pre>
<h3 id="教师表-Teacher"><a href="#教师表-Teacher" class="headerlink" title="教师表 Teacher"></a>教师表 Teacher</h3><pre><code class="hljs sql"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> Teacher(TId <span class="hljs-built_in">varchar</span>(<span class="hljs-number">10</span>),Tname <span class="hljs-built_in">varchar</span>(<span class="hljs-number">10</span>));
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> Teacher <span class="hljs-keyword">values</span>(<span class="hljs-string">'01'</span> , <span class="hljs-string">'张三'</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> Teacher <span class="hljs-keyword">values</span>(<span class="hljs-string">'02'</span> , <span class="hljs-string">'李四'</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> Teacher <span class="hljs-keyword">values</span>(<span class="hljs-string">'03'</span> , <span class="hljs-string">'王五'</span>);</code></pre>
<h3 id="成绩表-SC"><a href="#成绩表-SC" class="headerlink" title="成绩表 SC"></a>成绩表 SC</h3><pre><code class="hljs sql"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> SC(<span class="hljs-keyword">SId</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">10</span>),CId <span class="hljs-built_in">varchar</span>(<span class="hljs-number">10</span>),score <span class="hljs-built_in">decimal</span>(<span class="hljs-number">18</span>,<span class="hljs-number">1</span>));
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> SC <span class="hljs-keyword">values</span>(<span class="hljs-string">'01'</span> , <span class="hljs-string">'01'</span> , <span class="hljs-number">80</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> SC <span class="hljs-keyword">values</span>(<span class="hljs-string">'01'</span> , <span class="hljs-string">'02'</span> , <span class="hljs-number">90</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> SC <span class="hljs-keyword">values</span>(<span class="hljs-string">'01'</span> , <span class="hljs-string">'03'</span> , <span class="hljs-number">99</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> SC <span class="hljs-keyword">values</span>(<span class="hljs-string">'02'</span> , <span class="hljs-string">'01'</span> , <span class="hljs-number">70</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> SC <span class="hljs-keyword">values</span>(<span class="hljs-string">'02'</span> , <span class="hljs-string">'02'</span> , <span class="hljs-number">60</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> SC <span class="hljs-keyword">values</span>(<span class="hljs-string">'02'</span> , <span class="hljs-string">'03'</span> , <span class="hljs-number">80</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> SC <span class="hljs-keyword">values</span>(<span class="hljs-string">'03'</span> , <span class="hljs-string">'01'</span> , <span class="hljs-number">80</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> SC <span class="hljs-keyword">values</span>(<span class="hljs-string">'03'</span> , <span class="hljs-string">'02'</span> , <span class="hljs-number">80</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> SC <span class="hljs-keyword">values</span>(<span class="hljs-string">'03'</span> , <span class="hljs-string">'03'</span> , <span class="hljs-number">80</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> SC <span class="hljs-keyword">values</span>(<span class="hljs-string">'04'</span> , <span class="hljs-string">'01'</span> , <span class="hljs-number">50</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> SC <span class="hljs-keyword">values</span>(<span class="hljs-string">'04'</span> , <span class="hljs-string">'02'</span> , <span class="hljs-number">30</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> SC <span class="hljs-keyword">values</span>(<span class="hljs-string">'04'</span> , <span class="hljs-string">'03'</span> , <span class="hljs-number">20</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> SC <span class="hljs-keyword">values</span>(<span class="hljs-string">'05'</span> , <span class="hljs-string">'01'</span> , <span class="hljs-number">76</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> SC <span class="hljs-keyword">values</span>(<span class="hljs-string">'05'</span> , <span class="hljs-string">'02'</span> , <span class="hljs-number">87</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> SC <span class="hljs-keyword">values</span>(<span class="hljs-string">'06'</span> , <span class="hljs-string">'01'</span> , <span class="hljs-number">31</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> SC <span class="hljs-keyword">values</span>(<span class="hljs-string">'06'</span> , <span class="hljs-string">'03'</span> , <span class="hljs-number">34</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> SC <span class="hljs-keyword">values</span>(<span class="hljs-string">'07'</span> , <span class="hljs-string">'02'</span> , <span class="hljs-number">89</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> SC <span class="hljs-keyword">values</span>(<span class="hljs-string">'07'</span> , <span class="hljs-string">'03'</span> , <span class="hljs-number">98</span>);</code></pre>
<h2 id="练习题目"><a href="#练习题目" class="headerlink" title="练习题目"></a>练习题目</h2><ol>
<li><p>查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数</p>
<p>1.1 查询同时存在” 01 “课程和” 02 “课程的情况</p>
<p>1.2 查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )</p>
<p>1.3 查询不存在” 01 “课程但存在” 02 “课程的情况</p>
</li>
<li><p>查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩</p>
</li>
<li><p>查询在 SC 表存在成绩的学生信息</p>
</li>
<li><p>查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )</p>
<p>4.1 查有成绩的学生信息</p>
</li>
<li><p>查询「李」姓老师的数量</p>
</li>
<li><p>查询学过「张三」老师授课的同学的信息</p>
</li>
<li><p>查询没有学全所有课程的同学的信息</p>
</li>
<li><p>查询至少有一门课与学号为” 01 “的同学所学相同的同学的信息</p>
</li>
<li><p>查询和” 01 “号的同学学习的课程 完全相同的其他同学的信息</p>
</li>
<li><p>查询没学过”张三”老师讲授的任一门课程的学生姓名</p>
</li>
<li><p>查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩</p>
</li>
<li><p>检索” 01 “课程分数小于 60，按分数降序排列的学生信息</p>
</li>
<li><p>按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩</p>
</li>
<li><p>查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率。及格为&gt;=60，中等为：70-80，优良为：80-90，优秀为：&gt;=90。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列</p>
</li>
<li><p>按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺</p>
<p>15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次</p>
</li>
<li><p>查询学生的总成绩，并进行排名，总分重复时保留名次空缺</p>
<p>16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺</p>
</li>
<li><p>统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比</p>
</li>
<li>查询各科成绩前三名的记录</li>
<li>查询每门课程被选修的学生数</li>
<li>查询出只选修两门课程的学生学号和姓名</li>
<li>查询男生、女生人数</li>
<li>查询名字中含有「风」字的学生信息</li>
<li>查询同名同性学生名单，并统计同名人数</li>
<li>查询 1990 年出生的学生名单</li>
<li>查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列</li>
<li>查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩</li>
<li>查询课程名称为「数学」，且分数低于 60 的学生姓名和分数</li>
<li>查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）</li>
<li>查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数</li>
<li>查询不及格的课程</li>
<li>查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名</li>
<li>求每门课程的学生人数</li>
<li>成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩</li>
<li>成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩</li>
<li>查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩</li>
<li>查询每门功成绩最好的前两名</li>
<li>统计每门课程的学生选修人数（超过 5 人的课程才统计）。</li>
<li>检索至少选修两门课程的学生学号</li>
<li>查询选修了全部课程的学生信息</li>
<li>查询各学生的年龄，只按年份来算</li>
<li>按照出生日期来算，当前月日 &lt; 出生年月的月日则，年龄减一</li>
<li>查询本周过生日的学生</li>
<li>查询下周过生日的学生</li>
<li>查询本月过生日的学生</li>
<li>查询下月过生日的学生</li>
</ol>
<h2 id="题目答案"><a href="#题目答案" class="headerlink" title="题目答案"></a>题目答案</h2><ol>
<li><p>查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数。因为需要全部的学生信息，则需要在sc表中得到符合条件的SId后与student表进行join，可以left join 也可以 right join</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> Student <span class="hljs-keyword">RIGHT</span> <span class="hljs-keyword">JOIN</span> (
    <span class="hljs-keyword">select</span> t1.SId, class1, class2 <span class="hljs-keyword">from</span>
          (<span class="hljs-keyword">select</span> <span class="hljs-keyword">SId</span>, score <span class="hljs-keyword">as</span> class1 <span class="hljs-keyword">from</span> sc <span class="hljs-keyword">where</span> sc.CId = <span class="hljs-string">'01'</span>)<span class="hljs-keyword">as</span> t1, 
          (<span class="hljs-keyword">select</span> <span class="hljs-keyword">SId</span>, score <span class="hljs-keyword">as</span> class2 <span class="hljs-keyword">from</span> sc <span class="hljs-keyword">where</span> sc.CId = <span class="hljs-string">'02'</span>)<span class="hljs-keyword">as</span> t2
    <span class="hljs-keyword">where</span> t1.SId = t2.SId <span class="hljs-keyword">AND</span> t1.class1 &gt; t2.class2
)r 
<span class="hljs-keyword">on</span> Student.SId = r.SId;</code></pre>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span>  (
    <span class="hljs-keyword">select</span> t1.SId, class1, class2 
    <span class="hljs-keyword">from</span>
        (<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">SId</span>, score <span class="hljs-keyword">as</span> class1 <span class="hljs-keyword">FROM</span> sc <span class="hljs-keyword">WHERE</span> sc.CId = <span class="hljs-string">'01'</span>) <span class="hljs-keyword">AS</span> t1, 
        (<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">SId</span>, score <span class="hljs-keyword">as</span> class2 <span class="hljs-keyword">FROM</span> sc <span class="hljs-keyword">WHERE</span> sc.CId = <span class="hljs-string">'02'</span>) <span class="hljs-keyword">AS</span> t2
    <span class="hljs-keyword">where</span> t1.SId = t2.SId <span class="hljs-keyword">and</span> t1.class1 &gt; t2.class2
) r
<span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span> Student
<span class="hljs-keyword">ON</span> Student.SId = r.SId;</code></pre>
<p>1.1 查询同时存在” 01 “课程和” 02 “课程的情况</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> 
    (<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> sc <span class="hljs-keyword">where</span> sc.CId = <span class="hljs-string">'01'</span>) <span class="hljs-keyword">as</span> t1, 
    (<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> sc <span class="hljs-keyword">where</span> sc.CId = <span class="hljs-string">'02'</span>) <span class="hljs-keyword">as</span> t2
<span class="hljs-keyword">where</span> t1.SId = t2.SId;</code></pre>
<p>1.2 查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> 
(<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> sc <span class="hljs-keyword">where</span> sc.CId = <span class="hljs-string">'01'</span>) <span class="hljs-keyword">as</span> t1
<span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> 
(<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> sc <span class="hljs-keyword">where</span> sc.CId = <span class="hljs-string">'02'</span>) <span class="hljs-keyword">as</span> t2
<span class="hljs-keyword">on</span> t1.SId = t2.SId;</code></pre>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> 
(<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> sc <span class="hljs-keyword">where</span> sc.CId = <span class="hljs-string">'02'</span>) <span class="hljs-keyword">as</span> t2
<span class="hljs-keyword">right</span> <span class="hljs-keyword">join</span> 
(<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> sc <span class="hljs-keyword">where</span> sc.CId = <span class="hljs-string">'01'</span>) <span class="hljs-keyword">as</span> t1
<span class="hljs-keyword">on</span> t1.SId = t2.SId;</code></pre>
<p>1.3 查询不存在” 01 “课程但存在” 02 “课程的情况</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> sc
<span class="hljs-keyword">where</span> sc.SId <span class="hljs-keyword">not</span> <span class="hljs-keyword">in</span> (
    <span class="hljs-keyword">select</span> <span class="hljs-keyword">SId</span> <span class="hljs-keyword">from</span> sc 
    <span class="hljs-keyword">where</span> sc.CId = <span class="hljs-string">'01'</span>
) 
<span class="hljs-keyword">AND</span> sc.CId= <span class="hljs-string">'02'</span>;</code></pre>
</li>
<li><p>查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩。根据学生ID把成绩分组，对分组中的score求平均值，最后在选取结果中AVG大于60的即可</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> student.SId,sname,ss <span class="hljs-keyword">from</span> student,(
    <span class="hljs-keyword">select</span> <span class="hljs-keyword">SId</span>, <span class="hljs-keyword">AVG</span>(score) <span class="hljs-keyword">as</span> ss <span class="hljs-keyword">from</span> sc  
    <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> <span class="hljs-keyword">SId</span> 
    <span class="hljs-keyword">HAVING</span> <span class="hljs-keyword">AVG</span>(score)&gt; <span class="hljs-number">60</span>
    )r
<span class="hljs-keyword">where</span> student.sid = r.sid;</code></pre>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> Student.SId, Student.Sname, r.ss <span class="hljs-keyword">from</span> Student <span class="hljs-keyword">right</span> <span class="hljs-keyword">join</span>(
      <span class="hljs-keyword">select</span> <span class="hljs-keyword">SId</span>, <span class="hljs-keyword">AVG</span>(score) <span class="hljs-keyword">AS</span> ss <span class="hljs-keyword">from</span> sc
      <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> <span class="hljs-keyword">SId</span>
      <span class="hljs-keyword">HAVING</span> <span class="hljs-keyword">AVG</span>(score)&gt; <span class="hljs-number">60</span>
)r <span class="hljs-keyword">on</span> Student.SId = r.SId;</code></pre>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> s.SId,ss,Sname <span class="hljs-keyword">from</span>(
<span class="hljs-keyword">select</span> <span class="hljs-keyword">SId</span>, <span class="hljs-keyword">AVG</span>(score) <span class="hljs-keyword">as</span> ss <span class="hljs-keyword">from</span> sc  
<span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> <span class="hljs-keyword">SId</span> 
<span class="hljs-keyword">HAVING</span> <span class="hljs-keyword">AVG</span>(score)&gt; <span class="hljs-number">60</span>
)r <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> 
(<span class="hljs-keyword">select</span> Student.SId, Student.Sname <span class="hljs-keyword">from</span>
Student)s <span class="hljs-keyword">on</span> s.SId = r.SId;</code></pre>
</li>
<li><p>查询在 SC 表存在成绩的学生信息</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-keyword">DISTINCT</span> student.*
<span class="hljs-keyword">from</span> student,sc
<span class="hljs-keyword">where</span> student.SId=sc.SId</code></pre>
</li>
<li><p>查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和。联合查询不显示没选课的学生</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> student.sid, student.sname,r.coursenumber,r.scoresum
<span class="hljs-keyword">from</span> student,
(<span class="hljs-keyword">select</span> sc.sid, <span class="hljs-keyword">sum</span>(sc.score) <span class="hljs-keyword">as</span> scoresum, <span class="hljs-keyword">count</span>(sc.cid) <span class="hljs-keyword">as</span> coursenumber <span class="hljs-keyword">from</span> sc 
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> sc.sid)r
<span class="hljs-keyword">where</span> student.sid = r.sid;</code></pre>
<p>显示没选课的学生</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> s.sid, s.sname,r.coursenumber,r.scoresum
<span class="hljs-keyword">from</span> (
    (<span class="hljs-keyword">select</span> student.sid,student.sname 
    <span class="hljs-keyword">from</span> student
    )s 
    <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> 
    (<span class="hljs-keyword">select</span> 
        sc.sid, <span class="hljs-keyword">sum</span>(sc.score) <span class="hljs-keyword">as</span> scoresum, <span class="hljs-keyword">count</span>(sc.cid) <span class="hljs-keyword">as</span> coursenumber
        <span class="hljs-keyword">from</span> sc 
        <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> sc.sid
    )r 
   <span class="hljs-keyword">on</span> s.sid = r.sid
);</code></pre>
<p>4.1 查有成绩的学生信息</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> student 
<span class="hljs-keyword">where</span> <span class="hljs-keyword">exists</span> (<span class="hljs-keyword">select</span> sc.sid <span class="hljs-keyword">from</span> sc <span class="hljs-keyword">where</span> student.sid = sc.sid);</code></pre>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> student
<span class="hljs-keyword">where</span> student.sid <span class="hljs-keyword">in</span> (<span class="hljs-keyword">select</span> sc.sid <span class="hljs-keyword">from</span> sc);</code></pre>
</li>
<li><p>查询「李」姓老师的数量</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-keyword">count</span>(*)
<span class="hljs-keyword">from</span> teacher
<span class="hljs-keyword">where</span> tname <span class="hljs-keyword">like</span> <span class="hljs-string">'李%'</span>;</code></pre>
</li>
<li><p>查询学过「张三」老师授课的同学的信息</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> student.* <span class="hljs-keyword">from</span> student,teacher,course,sc
<span class="hljs-keyword">where</span> 
    student.sid = sc.sid 
    <span class="hljs-keyword">and</span> course.cid=sc.cid 
    <span class="hljs-keyword">and</span> course.tid = teacher.tid 
    <span class="hljs-keyword">and</span> tname = <span class="hljs-string">'张三'</span>;</code></pre>
</li>
<li><p>查询没有学全所有课程的同学的信息</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> student
<span class="hljs-keyword">where</span> student.sid <span class="hljs-keyword">not</span> <span class="hljs-keyword">in</span> (
  <span class="hljs-keyword">select</span> sc.sid <span class="hljs-keyword">from</span> sc
  <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> sc.sid
  <span class="hljs-keyword">having</span> <span class="hljs-keyword">count</span>(sc.cid)= (<span class="hljs-keyword">select</span> <span class="hljs-keyword">count</span>(cid) <span class="hljs-keyword">from</span> course)
);</code></pre>
</li>
<li><p>查询至少有一门课与学号为” 01 “的同学所学相同的同学的信息</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> student 
<span class="hljs-keyword">where</span> student.sid <span class="hljs-keyword">in</span> (
    <span class="hljs-keyword">select</span> sc.sid <span class="hljs-keyword">from</span> sc 
    <span class="hljs-keyword">where</span> sc.cid <span class="hljs-keyword">in</span>(
        <span class="hljs-keyword">select</span> sc.cid <span class="hljs-keyword">from</span> sc 
        <span class="hljs-keyword">where</span> sc.sid = <span class="hljs-string">'01'</span>
    )
);</code></pre>
</li>
<li><p>查询和” 01 “号的同学学习的课程完全相同的其他同学的信息</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> student.* <span class="hljs-keyword">from</span> student 
<span class="hljs-keyword">right</span> <span class="hljs-keyword">join</span> (
	<span class="hljs-keyword">select</span> b.sid <span class="hljs-keyword">from</span> (
		<span class="hljs-keyword">select</span> <span class="hljs-keyword">sid</span> <span class="hljs-keyword">from</span> SC <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> (
			<span class="hljs-keyword">select</span> cid <span class="hljs-keyword">from</span> sc <span class="hljs-keyword">where</span> <span class="hljs-keyword">sid</span>=<span class="hljs-string">'01'</span>
		)a <span class="hljs-keyword">on</span> sc.CId=a.CId
	)b <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> b.SId 
	<span class="hljs-keyword">having</span> <span class="hljs-keyword">COUNT</span>(b.sid)&gt;<span class="hljs-number">2</span> <span class="hljs-keyword">and</span> b.SId &gt; <span class="hljs-string">'01'</span>
)c <span class="hljs-keyword">on</span> Student.SId=c.SId</code></pre>
</li>
<li><p>查询没学过”张三”老师讲授的任一门课程的学生姓名</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> student
    <span class="hljs-keyword">where</span> student.sid <span class="hljs-keyword">not</span> <span class="hljs-keyword">in</span>(
        <span class="hljs-keyword">select</span> sc.sid <span class="hljs-keyword">from</span> sc <span class="hljs-keyword">where</span> sc.cid <span class="hljs-keyword">in</span>(
            <span class="hljs-keyword">select</span> course.cid <span class="hljs-keyword">from</span> course <span class="hljs-keyword">where</span> course.tid <span class="hljs-keyword">in</span>(
                <span class="hljs-keyword">select</span> teacher.tid <span class="hljs-keyword">from</span> teacher <span class="hljs-keyword">where</span> tname = <span class="hljs-string">"张三"</span>
            )
        )
    );</code></pre>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> student
<span class="hljs-keyword">where</span> student.sid <span class="hljs-keyword">not</span> <span class="hljs-keyword">in</span>(
    <span class="hljs-keyword">select</span> sc.sid <span class="hljs-keyword">from</span> sc,course,teacher 
    <span class="hljs-keyword">where</span>
        sc.cid = course.cid
        <span class="hljs-keyword">and</span> course.tid = teacher.tid
        <span class="hljs-keyword">and</span> teacher.tname= <span class="hljs-string">"张三"</span>
);</code></pre>
</li>
<li><p>查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> student.sid, student.sname, <span class="hljs-keyword">AVG</span>(sc.score) <span class="hljs-keyword">from</span> student,sc
<span class="hljs-keyword">where</span> 
    student.sid = sc.sid <span class="hljs-keyword">and</span> sc.score&lt;<span class="hljs-number">60</span>
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> sc.sid 
<span class="hljs-keyword">having</span> <span class="hljs-keyword">count</span>(*)&gt;<span class="hljs-number">1</span>;</code></pre>
</li>
<li><p>检索” 01 “课程分数小于 60，按分数降序排列的学生信息</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> student.*, sc.score <span class="hljs-keyword">from</span> student, sc
<span class="hljs-keyword">where</span> student.sid = sc.sid
<span class="hljs-keyword">and</span> sc.score &lt; <span class="hljs-number">60</span>
<span class="hljs-keyword">and</span> cid = <span class="hljs-string">"01"</span>
<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> sc.score <span class="hljs-keyword">DESC</span>;</code></pre>
</li>
<li><p>按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> *  <span class="hljs-keyword">from</span> sc 
<span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> (
    <span class="hljs-keyword">select</span> <span class="hljs-keyword">sid</span>,<span class="hljs-keyword">avg</span>(score) <span class="hljs-keyword">as</span> avscore <span class="hljs-keyword">from</span> sc 
    <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">sid</span>
    )r 
<span class="hljs-keyword">on</span> sc.sid = r.sid
<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> avscore <span class="hljs-keyword">desc</span>;</code></pre>
</li>
<li><p>查询各科成绩最高分、最低分和平均分</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> 
sc.CId ,
<span class="hljs-keyword">max</span>(sc.score)<span class="hljs-keyword">as</span> 最高分,
<span class="hljs-keyword">min</span>(sc.score)<span class="hljs-keyword">as</span> 最低分,
<span class="hljs-keyword">AVG</span>(sc.score)<span class="hljs-keyword">as</span> 平均分,
<span class="hljs-keyword">count</span>(*)<span class="hljs-keyword">as</span> 选修人数,
<span class="hljs-keyword">sum</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> sc.score&gt;=<span class="hljs-number">60</span> <span class="hljs-keyword">then</span> <span class="hljs-number">1</span> <span class="hljs-keyword">else</span> <span class="hljs-number">0</span> <span class="hljs-keyword">end</span> )/<span class="hljs-keyword">count</span>(*)<span class="hljs-keyword">as</span> 及格率,
<span class="hljs-keyword">sum</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> sc.score&gt;=<span class="hljs-number">70</span> <span class="hljs-keyword">and</span> sc.score&lt;<span class="hljs-number">80</span> <span class="hljs-keyword">then</span> <span class="hljs-number">1</span> <span class="hljs-keyword">else</span> <span class="hljs-number">0</span> <span class="hljs-keyword">end</span> )/<span class="hljs-keyword">count</span>(*)<span class="hljs-keyword">as</span> 中等率,
<span class="hljs-keyword">sum</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> sc.score&gt;=<span class="hljs-number">80</span> <span class="hljs-keyword">and</span> sc.score&lt;<span class="hljs-number">90</span> <span class="hljs-keyword">then</span> <span class="hljs-number">1</span> <span class="hljs-keyword">else</span> <span class="hljs-number">0</span> <span class="hljs-keyword">end</span> )/<span class="hljs-keyword">count</span>(*)<span class="hljs-keyword">as</span> 优良率,
<span class="hljs-keyword">sum</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> sc.score&gt;=<span class="hljs-number">90</span> <span class="hljs-keyword">then</span> <span class="hljs-number">1</span> <span class="hljs-keyword">else</span> <span class="hljs-number">0</span> <span class="hljs-keyword">end</span> )/<span class="hljs-keyword">count</span>(*)<span class="hljs-keyword">as</span> 优秀率 
<span class="hljs-keyword">from</span> sc
<span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> sc.CId
<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> <span class="hljs-keyword">count</span>(*)<span class="hljs-keyword">DESC</span>, sc.CId <span class="hljs-keyword">ASC</span></code></pre>
</li>
<li><p>按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> a.cid, a.sid, a.score, <span class="hljs-keyword">count</span>(b.score)+<span class="hljs-number">1</span> <span class="hljs-keyword">as</span> <span class="hljs-keyword">rank</span>
<span class="hljs-keyword">from</span> sc <span class="hljs-keyword">as</span> a 
<span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> sc <span class="hljs-keyword">as</span> b 
<span class="hljs-keyword">on</span> a.score&lt;b.score <span class="hljs-keyword">and</span> a.cid = b.cid
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> a.cid, a.sid,a.score
<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> a.cid, <span class="hljs-keyword">rank</span> <span class="hljs-keyword">ASC</span>;</code></pre>
</li>
<li><p>查询学生的总成绩，并进行排名，总分重复时不保留名次空缺</p>
<pre><code class="hljs sql"><span class="hljs-keyword">set</span> @crank=<span class="hljs-number">0</span>;
<span class="hljs-keyword">select</span> q.sid, total, @crank := @crank +<span class="hljs-number">1</span> <span class="hljs-keyword">as</span> <span class="hljs-keyword">rank</span> <span class="hljs-keyword">from</span>(
<span class="hljs-keyword">select</span> sc.sid, <span class="hljs-keyword">sum</span>(sc.score) <span class="hljs-keyword">as</span> total <span class="hljs-keyword">from</span> sc
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> sc.sid
<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> total <span class="hljs-keyword">desc</span>)q;</code></pre>
</li>
<li><p>统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> course.cname, course.cid,
<span class="hljs-keyword">sum</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> sc.score&lt;=<span class="hljs-number">100</span> <span class="hljs-keyword">and</span> sc.score&gt;<span class="hljs-number">85</span> <span class="hljs-keyword">then</span> <span class="hljs-number">1</span> <span class="hljs-keyword">else</span> <span class="hljs-number">0</span> <span class="hljs-keyword">end</span>) <span class="hljs-keyword">as</span> <span class="hljs-string">"[100-85]"</span>,
<span class="hljs-keyword">sum</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> sc.score&lt;=<span class="hljs-number">85</span> <span class="hljs-keyword">and</span> sc.score&gt;<span class="hljs-number">70</span> <span class="hljs-keyword">then</span> <span class="hljs-number">1</span> <span class="hljs-keyword">else</span> <span class="hljs-number">0</span> <span class="hljs-keyword">end</span>) <span class="hljs-keyword">as</span> <span class="hljs-string">"[85-70]"</span>,
<span class="hljs-keyword">sum</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> sc.score&lt;=<span class="hljs-number">70</span> <span class="hljs-keyword">and</span> sc.score&gt;<span class="hljs-number">60</span> <span class="hljs-keyword">then</span> <span class="hljs-number">1</span> <span class="hljs-keyword">else</span> <span class="hljs-number">0</span> <span class="hljs-keyword">end</span>) <span class="hljs-keyword">as</span> <span class="hljs-string">"[70-60]"</span>,
<span class="hljs-keyword">sum</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> sc.score&lt;=<span class="hljs-number">60</span> <span class="hljs-keyword">and</span> sc.score&gt;<span class="hljs-number">0</span> <span class="hljs-keyword">then</span> <span class="hljs-number">1</span> <span class="hljs-keyword">else</span> <span class="hljs-number">0</span> <span class="hljs-keyword">end</span>) <span class="hljs-keyword">as</span> <span class="hljs-string">"[60-0]"</span>
<span class="hljs-keyword">from</span> sc <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> course
<span class="hljs-keyword">on</span> sc.cid = course.cid
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> sc.cid;</code></pre>
</li>
<li><p>查询各科成绩前三名的记录</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> sc
<span class="hljs-keyword">where</span> (
<span class="hljs-keyword">select</span> <span class="hljs-keyword">count</span>(*) <span class="hljs-keyword">from</span> sc <span class="hljs-keyword">as</span> a 
<span class="hljs-keyword">where</span> sc.cid = a.cid <span class="hljs-keyword">and</span> sc.score&lt;a.score 
)&lt; <span class="hljs-number">3</span>
<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> cid <span class="hljs-keyword">asc</span>, sc.score <span class="hljs-keyword">desc</span>;</code></pre>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> a.sid,a.cid,a.score <span class="hljs-keyword">from</span> sc a 
<span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> sc b <span class="hljs-keyword">on</span> a.cid = b.cid <span class="hljs-keyword">and</span> a.score&lt;b.score
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> a.cid, a.sid
<span class="hljs-keyword">having</span> <span class="hljs-keyword">count</span>(b.cid)&lt;<span class="hljs-number">3</span>
<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> a.cid;</code></pre>
</li>
<li><p>查询每门课程被选修的学生数</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> cid, <span class="hljs-keyword">count</span>(<span class="hljs-keyword">sid</span>) <span class="hljs-keyword">from</span> sc 
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> cid;</code></pre>
</li>
<li><p>查询出只选修两门课程的学生学号和姓名</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> student.sid, student.sname <span class="hljs-keyword">from</span> student
<span class="hljs-keyword">where</span> student.sid <span class="hljs-keyword">in</span>
(<span class="hljs-keyword">select</span> sc.sid <span class="hljs-keyword">from</span> sc
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> sc.sid
<span class="hljs-keyword">having</span> <span class="hljs-keyword">count</span>(sc.cid)=<span class="hljs-number">2</span>
);</code></pre>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> student.SId,student.Sname
<span class="hljs-keyword">from</span> sc,student
<span class="hljs-keyword">where</span> student.SId=sc.SId  
<span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> sc.SId
<span class="hljs-keyword">HAVING</span> <span class="hljs-keyword">count</span>(*)=<span class="hljs-number">2</span>；</code></pre>
</li>
<li><p>查询男生、女生人数</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> ssex, <span class="hljs-keyword">count</span>(*) <span class="hljs-keyword">from</span> student
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> ssex;</code></pre>
</li>
<li><p>查询名字中含有「风」字的学生信息</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> *
<span class="hljs-keyword">from</span> student 
<span class="hljs-keyword">where</span> student.Sname <span class="hljs-keyword">like</span> <span class="hljs-string">'%风%'</span></code></pre>
</li>
<li><p>查询同名学生名单，并统计同名人数</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> sname, <span class="hljs-keyword">count</span>(*) <span class="hljs-keyword">from</span> student
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> sname
<span class="hljs-keyword">having</span> <span class="hljs-keyword">count</span>(*)&gt;<span class="hljs-number">1</span>;</code></pre>
</li>
<li><p>查询 1990 年出生的学生名单</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> *
<span class="hljs-keyword">from</span> student
<span class="hljs-keyword">where</span> <span class="hljs-keyword">YEAR</span>(student.Sage)=<span class="hljs-number">1990</span>;</code></pre>
</li>
<li><p>查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> sc.cid, course.cname, <span class="hljs-keyword">AVG</span>(SC.SCORE) <span class="hljs-keyword">as</span> average <span class="hljs-keyword">from</span> sc, course
<span class="hljs-keyword">where</span> sc.cid = course.cid
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> sc.cid 
<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> average <span class="hljs-keyword">desc</span>,cid <span class="hljs-keyword">asc</span>;</code></pre>
</li>
<li><p>查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> student.sid, student.sname, <span class="hljs-keyword">AVG</span>(sc.score) <span class="hljs-keyword">as</span> aver <span class="hljs-keyword">from</span> student, sc
<span class="hljs-keyword">where</span> student.sid = sc.sid
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> sc.sid
<span class="hljs-keyword">having</span> aver &gt; <span class="hljs-number">85</span>;</code></pre>
</li>
<li><p>查询课程名称为「数学」，且分数低于 60 的学生姓名和分数</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> student.sname, sc.score <span class="hljs-keyword">from</span> student, sc, course
<span class="hljs-keyword">where</span> student.sid = sc.sid
<span class="hljs-keyword">and</span> course.cid = sc.cid
<span class="hljs-keyword">and</span> course.cname = <span class="hljs-string">"数学"</span>
<span class="hljs-keyword">and</span> sc.score &lt; <span class="hljs-number">60</span>;</code></pre>
</li>
<li><p>查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> student.sname, cid, score <span class="hljs-keyword">from</span> student
<span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> sc
<span class="hljs-keyword">on</span> student.sid = sc.sid;</code></pre>
</li>
<li><p>查询任何一门课程成绩在 70 分以上学生的姓名、课程名称和分数</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> student.sname, course.cname,sc.score <span class="hljs-keyword">from</span> student,course,sc
<span class="hljs-keyword">where</span> sc.score&gt;<span class="hljs-number">70</span>
<span class="hljs-keyword">and</span> student.sid = sc.sid
<span class="hljs-keyword">and</span> sc.cid = course.cid;</code></pre>
</li>
<li><p>查询存在不及格的课程</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> cid <span class="hljs-keyword">from</span> sc
<span class="hljs-keyword">where</span> score&lt; <span class="hljs-number">60</span>
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> cid;</code></pre>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-keyword">DISTINCT</span> sc.CId
<span class="hljs-keyword">from</span> sc
<span class="hljs-keyword">where</span> sc.score &lt;<span class="hljs-number">60</span>;</code></pre>
</li>
<li><p>查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> student.sid,student.sname 
<span class="hljs-keyword">from</span> student,sc
<span class="hljs-keyword">where</span> cid=<span class="hljs-string">"01"</span>
<span class="hljs-keyword">and</span> score&gt;=<span class="hljs-number">80</span>
<span class="hljs-keyword">and</span> student.sid = sc.sid;</code></pre>
</li>
<li><p>求每门课程的学生人数</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> sc.CId,<span class="hljs-keyword">count</span>(*) <span class="hljs-keyword">as</span> 学生人数
<span class="hljs-keyword">from</span> sc
<span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> sc.CId;</code></pre>
</li>
<li><p>成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> student.*, sc.score, sc.cid <span class="hljs-keyword">from</span> student, teacher, course,sc 
<span class="hljs-keyword">where</span> teacher.tid = course.tid
<span class="hljs-keyword">and</span> sc.sid = student.sid
<span class="hljs-keyword">and</span> sc.cid = course.cid
<span class="hljs-keyword">and</span> teacher.tname = <span class="hljs-string">"张三"</span>
<span class="hljs-keyword">having</span> <span class="hljs-keyword">max</span>(sc.score);</code></pre>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> student.*, sc.score, sc.cid <span class="hljs-keyword">from</span> student, teacher, course,sc 
<span class="hljs-keyword">where</span> teacher.tid = course.tid
<span class="hljs-keyword">and</span> sc.sid = student.sid
<span class="hljs-keyword">and</span> sc.cid = course.cid
<span class="hljs-keyword">and</span> teacher.tname = <span class="hljs-string">"张三"</span>
<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> score <span class="hljs-keyword">desc</span>
<span class="hljs-keyword">limit</span> <span class="hljs-number">1</span>;</code></pre>
</li>
<li><p>成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> student.*, sc.score, sc.cid <span class="hljs-keyword">from</span> student, teacher, course,sc 
<span class="hljs-keyword">where</span> teacher.tid = course.tid
<span class="hljs-keyword">and</span> sc.sid = student.sid
<span class="hljs-keyword">and</span> sc.cid = course.cid
<span class="hljs-keyword">and</span> teacher.tname = <span class="hljs-string">"张三"</span>
<span class="hljs-keyword">and</span> sc.score = (
    <span class="hljs-keyword">select</span> <span class="hljs-keyword">Max</span>(sc.score) 
    <span class="hljs-keyword">from</span> sc,student, teacher, course
    <span class="hljs-keyword">where</span> teacher.tid = course.tid
    <span class="hljs-keyword">and</span> sc.sid = student.sid
    <span class="hljs-keyword">and</span> sc.cid = course.cid
    <span class="hljs-keyword">and</span> teacher.tname = <span class="hljs-string">"张三"</span>
);</code></pre>
</li>
<li><p>查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span>  a.cid, a.sid,  a.score <span class="hljs-keyword">from</span> sc <span class="hljs-keyword">as</span> a
<span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> 
sc <span class="hljs-keyword">as</span> b
<span class="hljs-keyword">on</span> a.sid = b.sid
<span class="hljs-keyword">and</span> a.cid != b.cid
<span class="hljs-keyword">and</span> a.score = b.score
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> cid, <span class="hljs-keyword">sid</span>;</code></pre>
</li>
<li><p>查询每门功成绩最好的前两名</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> a.sid,a.cid,a.score <span class="hljs-keyword">from</span> sc <span class="hljs-keyword">as</span> a 
<span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> sc <span class="hljs-keyword">as</span> b 
<span class="hljs-keyword">on</span> a.cid = b.cid <span class="hljs-keyword">and</span> a.score&lt;b.score
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> a.cid, a.sid
<span class="hljs-keyword">having</span> <span class="hljs-keyword">count</span>(b.cid)&lt;<span class="hljs-number">2</span>
<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> a.cid;</code></pre>
</li>
<li><p>统计每门课程的学生选修人数（超过 5 人的课程才统计）</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> sc.cid, <span class="hljs-keyword">count</span>(<span class="hljs-keyword">sid</span>) <span class="hljs-keyword">as</span> cc <span class="hljs-keyword">from</span> sc
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> cid
<span class="hljs-keyword">having</span> cc &gt;<span class="hljs-number">5</span>;</code></pre>
</li>
<li><p>检索至少选修两门课程的学生学号</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-keyword">sid</span>, <span class="hljs-keyword">count</span>(cid) <span class="hljs-keyword">as</span> cc <span class="hljs-keyword">from</span> sc
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">sid</span>
<span class="hljs-keyword">having</span> cc&gt;=<span class="hljs-number">2</span>;</code></pre>
</li>
<li><p>查询选修了全部课程的学生信息</p>
</li>
</ol>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> student.*
<span class="hljs-keyword">from</span> sc ,student 
<span class="hljs-keyword">where</span> sc.SId=student.SId
<span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> sc.SId
<span class="hljs-keyword">HAVING</span> <span class="hljs-keyword">count</span>(*) = (<span class="hljs-keyword">select</span> <span class="hljs-keyword">DISTINCT</span> <span class="hljs-keyword">count</span>(*) <span class="hljs-keyword">from</span> course )</code></pre>
<ol>
<li><p>查询各学生的年龄，只按年份来算</p>
<pre><code class="hljs plain"> </code></pre>
</li>
<li><p>按照出生日期来算，当前月日 &lt; 出生年月的月日则，年龄减一</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> student.SId <span class="hljs-keyword">as</span> 学生编号,student.Sname  <span class="hljs-keyword">as</span>  学生姓名,
<span class="hljs-keyword">TIMESTAMPDIFF</span>(<span class="hljs-keyword">YEAR</span>,student.Sage,<span class="hljs-keyword">CURDATE</span>()) <span class="hljs-keyword">as</span> 学生年龄
<span class="hljs-keyword">from</span> student</code></pre>
</li>
<li><p>查询本周过生日的学生</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> *
<span class="hljs-keyword">from</span> student 
<span class="hljs-keyword">where</span> <span class="hljs-keyword">WEEKOFYEAR</span>(student.Sage)=<span class="hljs-keyword">WEEKOFYEAR</span>(<span class="hljs-keyword">CURDATE</span>());</code></pre>
</li>
<li><p>查询下周过生日的学生</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> *
<span class="hljs-keyword">from</span> student 
<span class="hljs-keyword">where</span> <span class="hljs-keyword">WEEKOFYEAR</span>(student.Sage)=<span class="hljs-keyword">WEEKOFYEAR</span>(<span class="hljs-keyword">CURDATE</span>())+<span class="hljs-number">1</span>;</code></pre>
</li>
<li><p>查询本月过生日的学生</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> *
<span class="hljs-keyword">from</span> student 
<span class="hljs-keyword">where</span> <span class="hljs-keyword">MONTH</span>(student.Sage)=<span class="hljs-keyword">MONTH</span>(<span class="hljs-keyword">CURDATE</span>());</code></pre>
</li>
<li><p>查询下月过生日的学生</p>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> *
<span class="hljs-keyword">from</span> student 
<span class="hljs-keyword">where</span> <span class="hljs-keyword">MONTH</span>(student.Sage)=<span class="hljs-keyword">MONTH</span>(<span class="hljs-keyword">CURDATE</span>())+<span class="hljs-number">1</span>;</code></pre>
</li>
</ol>
<h2 id="参考文献"><a href="#参考文献" class="headerlink" title="参考文献"></a>参考文献</h2><p><a href="https://blog.csdn.net/huaxiawudi/article/details/82288044" target="_blank" rel="noopener">50道SQL练习题及答案与详细分析</a></p>

            </article>
            <hr>
            <div>
              <div class="post-metas mb-3">
                
                  <div class="post-meta mr-3">
                    <i class="iconfont icon-category"></i>
                    
                      <a class="hover-with-bg" href="/michuanblog/categories/%E9%9D%A2%E8%AF%95/">面试</a>
                    
                  </div>
                
                
                  <div class="post-meta">
                    <i class="iconfont icon-tags"></i>
                    
                      <a class="hover-with-bg" href="/michuanblog/tags/%E9%9D%A2%E8%AF%95/">面试</a>
                    
                      <a class="hover-with-bg" href="/michuanblog/tags/SQL/">SQL</a>
                    
                      <a class="hover-with-bg" href="/michuanblog/tags/MySQL/">MySQL</a>
                    
                  </div>
                
              </div>
              
                <p class="note note-warning">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-sa/4.0/deed.zh" target="_blank" rel="nofollow noopener noopener">CC BY-SA 4.0 协议</a> ，转载请注明出处！</p>
              
              
                <div class="post-prevnext row">
                  <div class="post-prev col-6">
                    
                    
                      <a href="/michuanblog/2020/08/15/Linux%E5%B8%B8%E7%94%A8%E5%91%BD%E4%BB%A4/">
                        <i class="iconfont icon-arrowleft"></i>
                        <span class="hidden-mobile">Linux常用命令</span>
                        <span class="visible-mobile">上一篇</span>
                      </a>
                    
                  </div>
                  <div class="post-next col-6">
                    
                    
                      <a href="/michuanblog/2020/08/13/%E9%9D%A2%E8%AF%95%E5%9F%BA%E7%A1%80%E7%9F%A5%E8%AF%86/">
                        <span class="hidden-mobile">面试基础知识</span>
                        <span class="visible-mobile">下一篇</span>
                        <i class="iconfont icon-arrowright"></i>
                      </a>
                    
                  </div>
                </div>
              
            </div>

            
              <!-- Comments -->
              <div class="comments" id="comments">
                
                
  <div id="vcomments"></div>
  <script type="text/javascript">
    function loadValine() {
      addScript('https://cdn.staticfile.org/valine/1.4.14/Valine.min.js', function () {
        new Valine({
          el: "#vcomments",
          app_id: "tKy1BysLG32PuX7L8s0rQi9C-gzGzoHsz",
          app_key: "wlDiPjGWwM0U49QD4CpxH5dz",
          placeholder: "说点什么",
          path: window.location.pathname,
          avatar: "retro",
          meta: ["nick","mail","link"],
          pageSize: "10",
          lang: "zh-CN",
          highlight: false,
          recordIP: false,
          serverURLs: "",
        });
      });
    }
    createObserver(loadValine, 'vcomments');
  </script>
  <noscript>Please enable JavaScript to view the <a href="https://valine.js.org" target="_blank" rel="nofollow noopener noopener">comments
      powered by Valine.</a></noscript>


              </div>
            
          </div>
        </div>
      </div>
    </div>
    
      <div class="d-none d-lg-block col-lg-2 toc-container" id="toc-ctn">
        <div id="toc">
  <p class="toc-header"><i class="iconfont icon-list"></i>&nbsp;目录</p>
  <div id="tocbot"></div>
</div>

      </div>
    
  </div>
</div>

<!-- Custom -->


    
  </main>

  
    <a id="scroll-top-button" href="#" role="button">
      <i class="iconfont icon-arrowup" aria-hidden="true"></i>
    </a>
  

  
    <div class="modal fade" id="modalSearch" tabindex="-1" role="dialog" aria-labelledby="ModalLabel"
     aria-hidden="true">
  <div class="modal-dialog modal-dialog-scrollable modal-lg" role="document">
    <div class="modal-content">
      <div class="modal-header text-center">
        <h4 class="modal-title w-100 font-weight-bold">搜索</h4>
        <button type="button" id="local-search-close" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body mx-3">
        <div class="md-form mb-5">
          <input type="text" id="local-search-input" class="form-control validate">
          <label data-error="x" data-success="v"
                 for="local-search-input">关键词</label>
        </div>
        <div class="list-group" id="local-search-result"></div>
      </div>
    </div>
  </div>
</div>
  

  
    <!-- APlayer 音乐播放器 -->
    <div id="aplayer"></div>
    <script defer src="https://cdn.staticfile.org/aplayer/1.10.1/APlayer.min.js" ></script>
<link  rel="stylesheet" href="https://cdn.staticfile.org/aplayer/1.10.1/APlayer.min.css" />
<script type="text/javascript">
  var oldLoadAp = window.onload;
  window.onload = function () {
    oldLoadAp && oldLoadAp();

    new APlayer({
      container: document.getElementById('aplayer'),
      fixed: true,
      autoplay: 'false' === 'true',
      loop: 'all',
      order: 'random',
      theme: '#b7daff',
      preload: 'none',
      audio: [{"name":"黄杨扁担","artist":"鹿女王","url":"http://mi_chuan.gitee.io/blog/黄杨扁担.mp3","cover":"http://mi_chuan.gitee.io/blog/黄杨扁担.png"},{"name":"隔世信","artist":"以冬","url":"http://mi_chuan.gitee.io/blog/隔世信.mp3","cover":"http://mi_chuan.gitee.io/blog/隔世信.jpg"}]
    });
  }
</script>

  

  

  <footer class="mt-5">
  <div class="text-center py-3">
    <div>
      <a href="https://hexo.io" target="_blank" rel="nofollow noopener"><span>Hexo</span></a>
      <i class="iconfont icon-love"></i>
      <a href="https://github.com/fluid-dev/hexo-theme-fluid" target="_blank" rel="nofollow noopener">
        <span>Fluid</span></a>
    </div>
    

    

    
  </div>
</footer>

<!-- SCRIPTS -->
<script  src="https://cdn.staticfile.org/jquery/3.4.1/jquery.min.js" ></script>
<script  src="https://cdn.staticfile.org/twitter-bootstrap/4.4.1/js/bootstrap.min.js" ></script>
<script  src="/michuanblog/js/debouncer.js" ></script>
<script  src="/michuanblog/js/main.js" ></script>

<!-- Plugins -->


  
    <script  src="/michuanblog/js/lazyload.js" ></script>
  



  <script defer src="https://cdn.staticfile.org/clipboard.js/2.0.6/clipboard.min.js" ></script>
  <script  src="/michuanblog/js/clipboard-use.js" ></script>







  <script  src="https://cdn.staticfile.org/tocbot/4.11.1/tocbot.min.js" ></script>
  <script>
    $(document).ready(function () {
      var boardCtn = $('#board-ctn');
      var boardTop = boardCtn.offset().top;

      tocbot.init({
        tocSelector: '#tocbot',
        contentSelector: 'article.markdown-body',
        headingSelector: 'h1,h2,h3,h4,h5,h6',
        linkClass: 'tocbot-link',
        activeLinkClass: 'tocbot-active-link',
        listClass: 'tocbot-list',
        isCollapsedClass: 'tocbot-is-collapsed',
        collapsibleClass: 'tocbot-is-collapsible',
        collapseDepth: 0,
        scrollSmooth: true,
        headingsOffset: -boardTop
      });
      if ($('.toc-list-item').length > 0) {
        $('#toc').css('visibility', 'visible');
      }
    });
  </script>



  <script  src="https://cdn.staticfile.org/typed.js/2.0.11/typed.min.js" ></script>
  <script>
    var typed = new Typed('#subtitle', {
      strings: [
        '  ',
        "50道SQL查询语句练习&nbsp;",
      ],
      cursorChar: "_",
      typeSpeed: 70,
      loop: false,
    });
    typed.stop();
    $(document).ready(function () {
      $(".typed-cursor").addClass("h2");
      typed.start();
    });
  </script>



  <script  src="https://cdn.staticfile.org/anchor-js/4.2.2/anchor.min.js" ></script>
  <script>
    anchors.options = {
      placement: "right",
      visible: "hover",
      
    };
    var el = "h1,h2,h3,h4,h5,h6".split(",");
    var res = [];
    for (item of el) {
      res.push(".markdown-body > " + item)
    }
    anchors.add(res.join(", "))
  </script>



  <script  src="/michuanblog/js/local-search.js" ></script>
  <script>
    var path = "/michuanblog/local-search.xml";
    var inputArea = document.querySelector("#local-search-input");
    inputArea.onclick = function () {
      searchFunc(path, 'local-search-input', 'local-search-result');
      this.onclick = null
    }
  </script>



  <script  src="https://cdn.staticfile.org/fancybox/3.5.7/jquery.fancybox.min.js" ></script>
  <link  rel="stylesheet" href="https://cdn.staticfile.org/fancybox/3.5.7/jquery.fancybox.min.css" />

  <script>
    $('#post img:not(.no-zoom img, img[no-zoom]), img[zoom]').each(
      function () {
        var element = document.createElement('a');
        $(element).attr('data-fancybox', 'images');
        $(element).attr('href', $(this).attr('src'));
        $(this).wrap(element);
      }
    );
  </script>





  

  
    <!-- MathJax -->
    <script>
      MathJax = {
        tex: {
          inlineMath: [['$', '$'], ['\\(', '\\)']]
        },
        options: {
          renderActions: {
            findScript: [10, doc => {
              document.querySelectorAll('script[type^="math/tex"]').forEach(node => {
                const display = !!node.type.match(/; *mode=display/);
                const math = new doc.options.MathItem(node.textContent, doc.inputJax[0], display);
                const text = document.createTextNode('');
                node.parentNode.replaceChild(text, node);
                math.start = { node: text, delim: '', n: 0 };
                math.end = { node: text, delim: '', n: 0 };
                doc.math.push(math);
              });
            }, '', false],
            insertedScript: [200, () => {
              document.querySelectorAll('mjx-container').forEach(node => {
                let target = node.parentNode;
                if (target.nodeName.toLowerCase() === 'li') {
                  target.parentNode.classList.add('has-jax');
                }
              });
            }, '', false]
          }
        }
      };
    </script>

    <script async src="https://cdn.staticfile.org/mathjax/3.0.5/es5/tex-svg.js" ></script>

  



  
  
    <script>
      !function (e, t, a) {
        function r() {
          for (var e = 0; e < s.length; e++) s[e].alpha <= 0 ? (t.body.removeChild(s[e].el), s.splice(e, 1)) : (s[e].y--, s[e].scale += .004, s[e].alpha -= .013, s[e].el.style.cssText = "left:" + s[e].x + "px;top:" + s[e].y + "px;opacity:" + s[e].alpha + ";transform:scale(" + s[e].scale + "," + s[e].scale + ") rotate(45deg);background:" + s[e].color + ";z-index:99999");
          requestAnimationFrame(r)
        }

        function n() {
          var t = "function" == typeof e.onclick && e.onclick;
          e.onclick = function (e) {
            t && t(), o(e)
          }
        }

        function o(e) {
          var a = t.createElement("div");
          a.className = "heart", s.push({
            el: a,
            x: e.clientX - 5,
            y: e.clientY - 5,
            scale: 1,
            alpha: 1,
            color: c()
          }), t.body.appendChild(a)
        }

        function i(e) {
          var a = t.createElement("style");
          a.type = "text/css";
          try {
            a.appendChild(t.createTextNode(e))
          } catch (t) {
            a.styleSheet.cssText = e
          }
          t.getElementsByTagName("head")[0].appendChild(a)
        }

        function c() {
          return "rgb(" + ~~(255 * Math.random()) + "," + ~~(255 * Math.random()) + "," + ~~(255 * Math.random()) + ")"
        }

        var s = [];
        e.requestAnimationFrame = e.requestAnimationFrame || e.webkitRequestAnimationFrame || e.mozRequestAnimationFrame || e.oRequestAnimationFrame || e.msRequestAnimationFrame || function (e) {
          setTimeout(e, 1e3 / 60)
        }, i(".heart{width: 10px;height: 10px;position: fixed;background: #f00;transform: rotate(45deg);-webkit-transform: rotate(45deg);-moz-transform: rotate(45deg);}.heart:after,.heart:before{content: '';width: inherit;height: inherit;background: inherit;border-radius: 50%;-webkit-border-radius: 50%;-moz-border-radius: 50%;position: fixed;}.heart:after{top: -5px;}.heart:before{left: -5px;}"), n(), r()
      }(window, document);
    </script>
  








  <script  src="https://cdn.staticfile.org/mermaid/8.5.0/mermaid.min.js" ></script>
  <script>
    if (window.mermaid) {
      mermaid.initialize({"theme":"default"});
    }
  </script>







<script src="/michuanblog/live2dw/lib/L2Dwidget.min.js?094cbace49a39548bed64abff5988b05"></script><script>L2Dwidget.init({"log":false,"pluginJsPath":"lib/","pluginModelPath":"assets/","pluginRootPath":"live2dw/","tagMode":false});</script></body>
</html>
